﻿using System;
using System.Collections.Generic;
using Community.CsharpSqlite.WP.SqliteDataProvider.Base;
using Community.CsharpSqlite.WP.SqliteDataProvider.Exceptions;
using Gwn.Utilities.Extensions;

namespace Community.CsharpSqlite.WP.SqliteDataProvider
{
    public class SqliteCommand : IDbCommand
    {
        private int _recordCount;
        private object _scalarResult;
        private string _errorMessage;
        private SqliteConnection _conn;
        private IDbTransaction _transaction;
        private IDataParameterCollection _parameters;

        /// <summary>
        /// Gets or sets the updated row source.
        /// </summary>
        /// <value>The updated row source.</value>
        public UpdateRowSource UpdatedRowSource { get; set; }

        /// <summary>
        /// Gets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDataParameterCollection Parameters
        {
            get { return _parameters; }
        }

        /// <summary>
        /// Gets or sets the connection.
        /// </summary>
        /// <value>The connection.</value>
        public IDbConnection Connection
        {
            // The user should be able to set or change the connection at 
            // any time.
            get { return _conn; }
            set
            {
                 // The connection is associated with the transaction
                 // so set the transaction object to return a null reference if the connection 
                 // is reset.
                if (_conn != value)
                    Transaction = null;

                _conn = (SqliteConnection)value;
            }
        }

        /// <summary>
        /// The sample only supports CommandType.Text.
        /// </summary>
        public CommandType CommandType
        {
            get { return CommandType.Text; }
            set { if (value != CommandType.Text) throw new NotSupportedException(); }
        }

        /// <summary>
        /// The sample does not support a command time-out. As a result,
        /// for the get, zero is returned because zero indicates an indefinite
        /// time-out period. For the set, throw an exception.
        /// </summary>
        public int CommandTimeout
        {
            get { return 0; }
            set { if (value != 0) throw new NotSupportedException(); }
        }

        /// <summary>
        /// Gets or sets the transaction.
        /// </summary>
        /// <value>The transaction.</value>
        public IDbTransaction Transaction
        {
            // Set the transaction. Consider additional steps to ensure that the transaction
            // is compatible with the connection, because the two are usually linked.
            get { return _transaction; }
            set { _transaction = (SqliteTransaction)value; }
        }
        

        #region PROPERTY: CommandText 
        private string _commandText;
        /// <summary>
        /// Gets or sets the command text.
        /// </summary>
        /// <value>The command text.</value>
        public string CommandText
        {
            get { return _commandText; }
            set { _commandText = value; }
        }

        #endregion

        #region PROPERTY: Parameter
        private SqliteParameter _myProperty;
        /// <summary>
        /// Gets or sets the Parameter.
        /// </summary>
        /// <value>The start date.</value>
        public SqliteParameter Parameter
        {
            get { return _myProperty; }
            set { _myProperty = value; }
        }
        #endregion
      
        #region CONSTRUCTOR: Overloaded 
        /// <summary>
        /// Initializes a new instance of the <see cref="SqliteCommand"/> class.
        /// </summary>
        /// <param name="commandText">The command text.</param>
        public SqliteCommand(string commandText)
        {
            _commandText = commandText;
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="SqliteCommand"/> class.
        /// </summary>
        /// <param name="commandText">The STR SQL.</param>
        /// <param name="conn">The conn.</param>
        public SqliteCommand(string commandText, SqliteConnection conn)
        {
            CommandText = commandText;
            _conn = conn;
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="SqliteCommand"/> class.
        /// </summary>
        public SqliteCommand()
        {
        }
        #endregion

        public IDataParameter CreateParameter()
        {
            return new SqliteParameter();
        }

        private void CheckConnection()
        {
            // There must be a valid and open connection.
            if (Connection == null || Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Connection must valid and open");

            // Reset error message
            _errorMessage = string.Empty;

        }

        /// <summary>
        /// ExecuteNonQuery is intended for commands that do
        /// not return results, instead returning only the number
        /// of records affected.
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            CheckConnection();

            InitializeCommand();

            var rc = Sqlite3.sqlite3_exec(
                _conn.Handle, 
                CommandText, 
                ProcessReceivedDataRow,  // callback
                null,                    // args
                ref _errorMessage);      // errors
            
            if (rc != Sqlite3.SQLITE_OK)
            {
                var errMsg = Sqlite3.sqlite3_errmsg(_conn.Handle);

                throw new SqliteConnectionException(string.Format(
                    "{0}\r\n{1}",
                    CommandText, errMsg));
            }
            return _recordCount;
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T ExecuteNonQuery<T>()
        {
            object results = ExecuteNonQuery();

            return results.GetTypeOf<T>();
        }

        /// <summary>
        /// ExecuteReader should retrieve results from the data source
        /// and return a DataReader that allows the user to process 
        /// the results.
        /// </summary>
        /// <returns></returns>
        public IDataReader ExecuteReader()
        {
            CheckConnection();

            InitializeCommand();

            var rc = Sqlite3.sqlite3_exec(
                _conn.Handle,
                CommandText,
                ProcessReceivedDataRow,  // callback
                null,                    // args
                ref _errorMessage);      // errors

            if (rc != Sqlite3.SQLITE_OK)
            {
                var errMsg = Sqlite3.sqlite3_errmsg(_conn.Handle);

                throw new SqliteConnectionException(string.Format(
                    "{0}\r\n{1}",
                    CommandText, errMsg));
            }

            return new SqliteDataReader(_rows);

        }


        public IDataReader ExecuteReader(CommandBehavior b)
        {
            throw new NotImplementedException();
        }


        /// <summary>
        /// Executes the query and returns the first column 
        /// of the first row of the result set
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            CheckConnection();

            InitializeCommand();

            var rc = Sqlite3.sqlite3_exec(
                _conn.Handle, 
                CommandText, 
                ProcessReceivedDataRow,  // callback
                null,                    // args
                ref _errorMessage);      // errors

            // if rc!=0 then there was an error
            if (rc != Sqlite3.SQLITE_OK)
            {
                var errMsg = Sqlite3.sqlite3_errmsg(_conn.Handle);

                throw new SqliteConnectionException(string.Format(
                    "{0}\r\n{1}",
                    CommandText, errMsg));
            }
            if (_rows.Count > 0)
                return _rows[0][0];

            return rc;
        }


        void InitializeCommand()
        {
            _rows.Clear();
            _scalarResult = null;   // first result of the first column
        }

        private List<DataRow> _rows = new List<DataRow>();

        /// <summary>
        /// Processes the received data row.
        /// </summary>
        /// <param name="pArg">The p arg.</param>
        /// <param name="nArg">The n arg.</param>
        /// <param name="azArgs">The az args.</param>
        /// <param name="azCols">The az cols.</param>
        /// <returns></returns>
        int ProcessReceivedDataRow(object pArg, Int64 nArg, object azArgs, object azCols)
        {
            int i;
            var azArg = (object[])azArgs;
            var azCol = (string[])azCols;

            var record = new DataRow();

            for (i = 0; i < nArg; i++)
            {
                var fieldName = azCol[i];
                var value = azArg[i];

                record[fieldName] = value??"<null>";
            }

            if (_scalarResult == null)
                _scalarResult = azArg[0];

            _rows.Add(record);

            _recordCount = _rows.Count; 

            return 0;
        }



        public void Cancel()
        {
            throw new NotSupportedException();
        }
    }
}
